package com.java.dao.impl;

import com.java.bean.Express;
import com.java.bean.Message;
import com.java.dao.BaseExpressDao;
import com.java.exception.DuplicateCodeException;
import com.java.exception.DuplicateNumberException;
import com.java.util.DruidUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @ClassName ExpressDaoMysql
 * @Description 实现类
 * @Author 0715-YuHao
 * @Date 2020/9/10 22:10
 * @Version 1.0
 */
public class ExpressDaoMysql extends DruidUtil implements BaseExpressDao {

    /**
     * @Author 0715-YuHao
     * @Description 用于查询数据库中的全部快递（总数+新增），待取件快递（总数+新增）
     * @Date 22:17 2020/9/10
     **/
    private static final String SQL_CONSOLE = "SELECT " +
            "COUNT(ID) as total_express, " +
            "COUNT(TO_DAYS(INTIME) = TO_DAYS(NOW()) OR NULL) as new_express, " +
            "COUNT(STATUS = 0 OR NULL) as wait_express, " +
            "COUNT(TO_DAYS(INTIME)=TO_DAYS(NOW()) AND STATUS=0 OR NULL) as new_wait_express " +
            "FROM express";
    /**
     * @Author 0715-YuHao
     * @Description 用于查询数据库中的所有快递信息
     * @Date 9:40 2020/9/11
     **/
    private static final String SQL_FIND_ALL = "SELECT * FROM express";
    /**
     * @Author 0715-YuHao
     * @Description 用于分页查询数据库中的快递信息
     * @Date 9:48 2020/9/11
     **/
    private static final String SQL_FIND_LIMIT = "SELECT * FROM express LIMIT ?,?";
    /**
     * @Author 0715-YuHao
     * @Description 用于通过快递单号查询快递信息
     * @Date 15:54 2020/9/11
     **/
    private static final String SQL_FIND_BY_NUMBER = "SELECT * FROM express WHERE number=?";
    /**
     * @Author 0715-YuHao
     * @Description 用于通过取件码查询快递信息
     * @Date 15:58 2020/9/11
     **/
    private static final String SQL_FIND_BY_CODE = "SELECT * FROM express WHERE code=?";
    /**
     * @Author 0715-YuHao
     * @Description 用于通过用户手机号码查询其所有的快递信息
     * @Date 16:00 2020/9/11
     **/
    private static final String SQL_FIND_BY_USERPHONE = "SELECT * FROM express WHERE userPhone=?";
    /**
     * @Author 0715-YuHao
     * @Description 用于通过用户手机号码和状态查询其所有的快递信息
     * @Date 16:00 2020/9/11
     **/
    private static final String SQL_FIND_BY_USERPHONE_AND_STATUS = "SELECT * FROM express WHERE userPhone=? AND status=?";
    /**
     * @Author 0715-YuHao
     * @Description 根据录入人手机号码查询其录入的所有快递信息
     * @Date 16:04 2020/9/11
     **/
    private static final String SQL_FIND_BY_SYSPHONE = "SELECT * FROM express WHERE sysPhone=?";
    /**
     * @Author 0715-YuHao
     * @Description 录入快递
     * @Date 16:09 2020/9/11
     **/
    private static final String SQL_INSERT = "INSERT INTO express VALUES (?,?,?,?,?,?,NOW(),?,0,?)";
    /**
     * @Author 0715-YuHao
     * @Description 修改快递
     * @Date 16:41 2020/9/11
     **/
    private static final String SQL_UPDATE = "UPDATE express SET number=?,username=?,company=?,status=? WHERE id=?";
    /**
     * @Author 0715-YuHao
     * @Description 通过取件码修改快递状态码
     * @Date 16:46 2020/9/11
     **/
    private static final String SQL_UPDATE_STATUS = "UPDATE express SET code=null,status=1,outTime=NOW() WHERE code=?";
    /**
     * @Author 0715-YuHao
     * @Description 删除快递
     * @Date 16:47 2020/9/11
     **/
    private static final String SQL_DELETE = "DELETE FROM express WHERE id=?";


    @Override
    public List<Map<String, Integer>> console() {
        //1.获取数据库连接
        Connection connection = getConnection();
        PreparedStatement state = null;
        ResultSet res = null;
        List<Map<String, Integer>> data = new ArrayList<>();
        try {
            //2.预编译SQL语句
            state = connection.prepareStatement(SQL_CONSOLE);
            //3.填充参数(可选)
            //4.执行SQL语句
            res = state.executeQuery();
            //5.获取执行的结果
            while (res.next()) {
                int total_express = res.getInt("total_express");
                int new_express = res.getInt("new_express");
                int wait_express = res.getInt("wait_express");
                int new_wait_express = res.getInt("new_wait_express");
                Map<String, Integer> data1 = new HashMap<>();
                data1.put("total_express", total_express);
                data1.put("new_express", new_express);
                data.add(data1);
                Map<String, Integer> data2 = new HashMap<>();
                data2.put("wait_express", wait_express);
                data2.put("new_wait_express", new_wait_express);
                data.add(data2);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //6.资源释放
            close(connection, state, res);
        }
        return data;
    }

    @Override
    public List<Express> findAll(boolean limit, int... args) {
        List<Express> list = new ArrayList<>();
        //1.获取数据库连接
        Connection connection = getConnection();
        PreparedStatement state = null;
        ResultSet res = null;
        try {
            if (limit) {
                //2.预编译SQL语句
                state = connection.prepareStatement(SQL_FIND_LIMIT);
                //3.填充参数
                state.setInt(1, args[0]);
                state.setInt(2, args[1]);
            } else {
                state = connection.prepareStatement(SQL_FIND_ALL);
            }
            //4.执行SQL语句
            res = state.executeQuery();
            //5.获取执行的结果
            while (res.next()) {
                list.add(new Express(
                        res.getInt("id"),
                        res.getString("number"),
                        res.getString("username"),
                        res.getString("userPhone"),
                        res.getString("company"),
                        res.getString("code"),
                        res.getTimestamp("inTime"),
                        res.getTimestamp("outTime"),
                        res.getInt("status"),
                        res.getString("sysPhone")
                ));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //6.资源释放
            close(connection, state, res);
        }
        return list;
    }

    @Override
    public Express findByNumber(String number) {
        //1.获取数据库连接
        Connection connection = getConnection();
        PreparedStatement state = null;
        ResultSet res = null;
        Express express = null;
        try {
            //2.预编译SQL语句
            state = connection.prepareStatement(SQL_FIND_BY_NUMBER);
            //3.填充参数(可选)
            state.setString(1, number);
            //4.执行SQL语句
            res = state.executeQuery();
            //5.获取执行的结果
            while (res.next()) {
                express = new Express(
                        res.getInt("id"),
                        res.getString("number"),
                        res.getString("username"),
                        res.getString("userPhone"),
                        res.getString("company"),
                        res.getString("code"),
                        res.getTimestamp("inTime"),
                        res.getTimestamp("outTime"),
                        res.getInt("status"),
                        res.getString("sysPhone")
                );
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //6.资源释放
            close(connection, state, res);
        }
        return express;
    }

    @Override
    public Express findByCode(String code) {
        //1.获取数据库连接
        Connection connection = getConnection();
        PreparedStatement state = null;
        ResultSet res = null;
        Express express = null;
        try {
            //2.预编译SQL语句
            state = connection.prepareStatement(SQL_FIND_BY_CODE);
            //3.填充参数(可选)
            state.setString(1, code);
            //4.执行SQL语句
            res = state.executeQuery();
            //5.获取执行的结果
            while (res.next()) {
                express = new Express(
                        res.getInt("id"),
                        res.getString("number"),
                        res.getString("username"),
                        res.getString("userPhone"),
                        res.getString("company"),
                        res.getString("code"),
                        res.getTimestamp("inTime"),
                        res.getTimestamp("outTime"),
                        res.getInt("status"),
                        res.getString("sysPhone")
                );
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //6.资源释放
            close(connection, state, res);
        }
        return express;
    }

    @Override
    public List<Express> findByUserPhone(String userPhone) {
        //1.获取数据库连接
        Connection connection = getConnection();
        PreparedStatement state = null;
        ResultSet res = null;
        Express express = null;
        List<Express> list = new ArrayList<>();
        try {
            //2.预编译SQL语句
            state = connection.prepareStatement(SQL_FIND_BY_USERPHONE);
            //3.填充参数(可选)
            state.setString(1, userPhone);
            //4.执行SQL语句
            res = state.executeQuery();
            //5.获取执行的结果
            while (res.next()) {
                list.add(new Express(
                        res.getInt("id"),
                        res.getString("number"),
                        res.getString("username"),
                        userPhone,
                        res.getString("company"),
                        res.getString("code"),
                        res.getTimestamp("inTime"),
                        res.getTimestamp("outTime"),
                        res.getInt("status"),
                        res.getString("sysPhone")
                ));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //6.资源释放
            close(connection, state, res);
        }
        return list;
    }

    @Override
    public List<Express> findByUserPhoneAndStatus(String userPhone, int status) {
        //1.获取数据库连接
        Connection connection = getConnection();
        PreparedStatement state = null;
        ResultSet res = null;
        Express express = null;
        List<Express> list = new ArrayList<>();
        try {
            //2.预编译SQL语句
            state = connection.prepareStatement(SQL_FIND_BY_USERPHONE_AND_STATUS);
            //3.填充参数(可选)
            state.setString(1, userPhone);
            state.setInt(2, status);
            //4.执行SQL语句
            res = state.executeQuery();
            //5.获取执行的结果
            while (res.next()) {
                list.add(new Express(
                        res.getInt("id"),
                        res.getString("number"),
                        res.getString("username"),
                        userPhone,
                        res.getString("company"),
                        res.getString("code"),
                        res.getTimestamp("inTime"),
                        res.getTimestamp("outTime"),
                        res.getInt("status"),
                        res.getString("sysPhone")
                ));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //6.资源释放
            close(connection, state, res);
        }
        return list;
    }

    @Override
    public List<Express> findBySysPhone(String sysPhone) {
        //1.获取数据库连接
        Connection connection = getConnection();
        PreparedStatement state = null;
        ResultSet res = null;
        Express express = null;
        List<Express> list = new ArrayList<>();
        try {
            //2.预编译SQL语句
            state = connection.prepareStatement(SQL_FIND_BY_SYSPHONE);
            //3.填充参数(可选)
            state.setString(1, sysPhone);
            //4.执行SQL语句
            res = state.executeQuery();
            //5.获取执行的结果
            while (res.next()) {
                list.add(new Express(
                        res.getInt("id"),
                        res.getString("number"),
                        res.getString("username"),
                        res.getString("userPhone"),
                        res.getString("company"),
                        res.getString("code"),
                        res.getTimestamp("inTime"),
                        res.getTimestamp("outTime"),
                        res.getInt("status"),
                        sysPhone
                ));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //6.资源释放
            close(connection, state, res);
        }
        return list;
    }

    @Override
    public boolean insert(Express e) throws DuplicateCodeException, DuplicateNumberException {
        //1.获取数据库连接
        Connection connection = getConnection();
        PreparedStatement state = null;
        Express express = null;
        try {
            //2.预编译SQL语句
            state = connection.prepareStatement(SQL_INSERT);
            //3.填充参数(可选)
            state.setNull(1, Types.INTEGER);
            state.setString(2, e.getNumber());
            state.setString(3, e.getUsername());
            state.setString(4, e.getUserPhone());
            state.setString(5, e.getCompany());
            state.setString(6, e.getCode());
            state.setNull(7, Types.TIMESTAMP);
            state.setString(8, e.getSysPhone());
            //4.执行SQL语句
            int i = state.executeUpdate();
            //5.获取执行的结果
            return i > 0;
        } catch (SQLException throwables) {
            //throwables.printStackTrace();
            if (throwables.getMessage().endsWith("for key 'CODE'")) {
                //是因为取件码重复,而出现了异常
                DuplicateCodeException exception = new DuplicateCodeException(throwables.getMessage());
                throw exception;
            }else if (throwables.getMessage().endsWith("for key 'number'")) {
                DuplicateNumberException exception = new DuplicateNumberException(throwables.getMessage());
                throw exception;
            }else {
                throwables.printStackTrace();
            }
        } finally {
            //6.资源释放
            close(connection, state, null);
        }
        return false;
    }


    @Override
    public boolean update(int id, Express newExpress) {
        //1.获取数据库连接
        Connection connection = getConnection();
        PreparedStatement state = null;
        Express express = null;
        try {
            //2.预编译SQL语句
            state = connection.prepareStatement(SQL_UPDATE);
            //3.填充参数(可选)
            state.setString(1, newExpress.getNumber());
            state.setString(2, newExpress.getUsername());
            state.setString(3, newExpress.getCompany());
            state.setInt(4, newExpress.getStatus());
            state.setInt(5, id);
            //4.执行SQL语句
            int i = state.executeUpdate();
            //5.获取执行的结果
            return i > 0;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //6.资源释放
            close(connection, state, null);
        }
        return false;
    }

    @Override
    public boolean updateStatus(String code) {
        //1.获取数据库连接
        Connection connection = getConnection();
        PreparedStatement state = null;
        Express express = null;
        try {
            //2.预编译SQL语句
            state = connection.prepareStatement(SQL_UPDATE_STATUS);
            //3.填充参数(可选)
            state.setString(1, code);
            //4.执行SQL语句
            int i = state.executeUpdate();
            //5.获取执行的结果
            return i > 0;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //6.资源释放
            close(connection, state, null);
        }
        return false;
    }

    @Override
    public boolean delete(int id) {
        //1.获取数据库连接
        Connection connection = getConnection();
        PreparedStatement state = null;
        Express express = null;
        try {
            //2.预编译SQL语句
            state = connection.prepareStatement(SQL_DELETE);
            //3.填充参数(可选)
            state.setInt(1, id);
            //4.执行SQL语句
            int i = state.executeUpdate();
            //5.获取执行的结果
            return i > 0;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //6.资源释放
            close(connection, state, null);
        }
        return false;
    }
}
